package com.cn.ccp.service.system;

import java.io.File;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.jfree.util.Log;
import org.springframework.jdbc.core.RowMapper;

import com.cn.ccp.action.BaseAction;
import com.cn.ccp.bean.UserBean;
import com.cn.ccp.service.BasicService;
import com.cn.ccp.util.CCPContants;
import com.cn.ccp.util.MsgConstants;

public class UserService extends BasicService {
	
	/**
	 * 用么是否存在
	 * @param userName
	 * @return
	 */
	public boolean userNameExsit(String userName){
		String sql = "SELECT COUNT(*) FROM USER WHERE  loginname = ?";
		return jdbcTemplate.queryForInt(sql,new Object[]{userName}) > 0 ;
	}
	
	
	
	public boolean checkLoginNameIsOk(String loginName){
		String sql = "SELECT COUNT(*) FROM USER WHERE  loginname = ?";
		return jdbcTemplate.queryForInt(sql,new Object[]{loginName}) == 0 ;
		
	}

	public boolean update(Object entity ,boolean isChangePassword ) {
		
		boolean flag = false ;
		UserBean userBean = (UserBean) entity;
		String oldImgFile = findById(userBean.getId()).getImage();
		
		try{
			if(!oldImgFile.equalsIgnoreCase(userBean.getImage())){
				File file = new File(CCPContants.SYS_PATH+File.separator+File.separator+oldImgFile);
				if(file.exists()) file.delete();
			}
		}catch(Exception e){
			Log.error("Delete old image file failed : ", e);
		}
		 
		 if(isChangePassword){
		 	String passwordSql = "select password(?)";
			String md5Password = (String) jdbcTemplate.queryForObject(passwordSql, new Object[]{userBean.getPassword()} ,String.class);
			
			userBean.setPassword(md5Password);
			String sql = "update  user set username=?, PASSWORD=?, dormitory_address=?, email=?, sex=?,mobile=?,user_type=?, home_address=?, birthdate=?, zip_code=?, admission_time=?,faculty_id=?, specialty_id=?, enrollment=?, image=?, grade=?, STATUS=? ,loginname = ?,descript = ?,file_path_id=?  where id = ?";
			flag = jdbcTemplate.update(sql ,new Object[]{userBean.getUserName(),userBean.getPassword(),
					userBean.getDormitoryAddress(),userBean.getEmail(),
					userBean.getSex(),userBean.getMobile(),
					userBean.getUserType() ,userBean.getHomeAddress(),
					userBean.getBirtdate(),userBean.getZipCode(),
					userBean.getAdmissionTime(),userBean.getFacultyId(),
					userBean.getSpecialtyId(),userBean.getEnrollment(),
					userBean.getImage(),userBean.getGrade(),
					userBean.getStatus(),userBean.getLogin_name() ,userBean.getDescript(),userBean.getFile_path_id(),userBean.getId()}) > 0;
		}else{
			String sql = "update  user set username=?, dormitory_address=?, email=?, sex=?,mobile=?,user_type=?, home_address=?, birthdate=?, zip_code=?, admission_time=?,faculty_id=?, specialty_id=?, enrollment=?, image=?, grade=?, STATUS=? ,loginname = ?,descript = ? ,file_path_id=? where id = ? ";
			flag = jdbcTemplate.update(sql ,new Object[]{userBean.getUserName(),
					userBean.getDormitoryAddress(),userBean.getEmail(),
					userBean.getSex(),userBean.getMobile(),
					userBean.getUserType() ,userBean.getHomeAddress(),
					userBean.getBirtdate(),userBean.getZipCode(),
					userBean.getAdmissionTime(),userBean.getFacultyId(),
					userBean.getSpecialtyId(),userBean.getEnrollment(),
					userBean.getImage(),userBean.getGrade(),
					userBean.getStatus(),userBean.getLogin_name() ,userBean.getDescript(),userBean.getFile_path_id(),userBean.getId()}) > 0;
			
		}
		 return flag ;
	}
	
	public void updateUserImage(String userId,String imagepath,String filePathId){
		String sql = "update  user set  image=?, file_path_id=? where id = ?";
		 jdbcTemplate.update(sql ,new Object[]{imagepath,filePathId,userId});
	}
	
	
	
	
	public boolean checkLoginUserNameIsOn(int id ,String loginUserName){
		String sql = "" ;
		Object[] objs = null ;
		if(id == 0){
			sql = "SELECT COUNT(1) FROM USER WHERE loginname = ? ";
			objs = new Object[]{loginUserName};
		}else{
			sql = "SELECT COUNT(1) FROM USER WHERE loginname = ? AND id != ?";
			objs = new Object[]{loginUserName ,id};
		}
		return jdbcTemplate.queryForInt(sql,objs) > 0; 
	}

	public Object loginForWebservice(String userName ,String password) {
		String sql = "SELECT u.* ,f.facultyname ,s.specialtyname ,gt.grade_type_name,rt.resource_type_name as userTypeName FROM USER u "+
						" LEFT OUTER JOIN faculty f ON f.id = u.faculty_id "+
						" LEFT OUTER JOIN specialty s ON s.id = u.specialty_id "+
						" LEFT OUTER JOIN grade_type gt ON gt.id = u.grade " +
						" LEFT OUTER JOIN resource_type rt ON rt.id = u.user_type " +
						" where u.loginname = ? and u.password = password(?)  " ;
		try {
			return jdbcTemplate.queryForObject(sql, new Object[] { userName, password}, new UsersMapper());
		} catch (Exception e) {
			return null;
		}
	}
	
	
	
	public Object loginCheck(String userName ,String password) {
//		password = BASE64Util.encode(password);
		String sql = "SELECT u.* ,f.facultyname ,s.specialtyname ,gt.grade_type_name,rt.resource_type_name as userTypeName FROM USER u "+
						" LEFT OUTER JOIN faculty f ON f.id = u.faculty_id "+
						" LEFT OUTER JOIN specialty s ON s.id = u.specialty_id "+
						" LEFT OUTER JOIN grade_type gt ON gt.id = u.grade " +
						" LEFT OUTER JOIN resource_type rt ON rt.id = u.user_type " +
						" where u.username = ? and u.password = password(?) and u.status !=-1" ;
		try {
			return jdbcTemplate.queryForObject(sql, new Object[] { userName, password}, new UsersMapper());
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}
	
	public Object loginCheckUseLoginName(String userName ,String password) {
//		password = BASE64Util.encode(password);
		String sql = "SELECT u.* ,f.facultyname ,s.specialtyname ,gt.grade_type_name,rt.resource_type_name as userTypeName FROM USER u "+
						" LEFT OUTER JOIN faculty f ON f.id = u.faculty_id "+
						" LEFT OUTER JOIN specialty s ON s.id = u.specialty_id "+
						" LEFT OUTER JOIN grade_type gt ON gt.id = u.grade " +
						" LEFT OUTER JOIN resource_type rt ON rt.id = u.user_type " +
						" where u.loginname = ? and u.password = password(?) and u.status !=-1" ;
		try {
			return jdbcTemplate.queryForObject(sql, new Object[] { userName, password}, new UsersMapper());
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}
	

	public UserBean findById(int id) {
		String sql = "SELECT u.* ,f.facultyname ,s.specialtyname ,gt.grade_type_name,rt.resource_type_name as userTypeName FROM  USER u "+
					" LEFT OUTER JOIN faculty f ON f.id = u.faculty_id "+
					" LEFT OUTER JOIN specialty s ON s.id = u.specialty_id "+
					" LEFT OUTER JOIN grade_type gt ON gt.id = u.grade " +
					" LEFT OUTER JOIN resource_type rt ON rt.id = u.user_type " +
					" where u.id = ?" ;
		return (UserBean) jdbcTemplate.queryForObject(sql, new Object[] { id }, new UsersMapper());
	}
	public List<UserBean> getUserByType(String specialtyId,String userType){
		String sql = "select id,username  FROM USER where specialty_id = ? and user_type = ?";
		return jdbcTemplate.query(sql, new Object[] { specialtyId,userType},new UsersSimpleMapper());
	}
	
	public List<UserBean> getAllTeachers(String facultyId){
		String sql = "select id,username  FROM USER where faculty_id = ? and user_type != 1";
		return jdbcTemplate.query(sql, new Object[] { facultyId},new UsersSimpleMapper());
	}
	
	public List<UserBean> getAllUsersList(){
		String sql = "select id,username  FROM USER ";
		return jdbcTemplate.query(sql,new UsersSimpleMapper());
	}
	
	
	public List<UserBean> getUserLIstFromRole(String roleId , String specialtyId){
		String sql = "SELECT u.id ,u.username FROM USER u "+
					" LEFT OUTER JOIN role_user ru ON ru.user_id = u.id "+
					" LEFT OUTER JOIN specialty s ON s.id = u.specialty_id "+
					" WHERE ru.role_id = ? AND u.specialty_id = ? ";
		return jdbcTemplate.query(sql ,new Object[]{roleId ,specialtyId}, new UsersSimpleMapper());
	}
	public List<UserBean> getUserLIstBySpecialty (String specialtyId){
		String sql = "SELECT u.id ,u.username FROM USER u WHERE  u.specialty_id = ? AND u.user_type = 2";
		return jdbcTemplate.query(sql ,new Object[]{specialtyId}, new UsersSimpleMapper());
	}
	
	
	
	
	
	public class UsersMapper implements RowMapper {
		public Object mapRow(ResultSet rs, int i) throws SQLException {
			UserBean userBean = new UserBean();
			userBean.setId(rs.getInt("id"));
			userBean.setLogin_name(rs.getString("loginname"));
			userBean.setUserName(rs.getString("username"));
			String password = rs.getString("password") ;
			userBean.setShowPassword(password);
			StringBuffer sb = new StringBuffer();
			for (int j = 0; j < password.length(); j++) 
				sb.append("*");
			userBean.setPassword(sb.toString());
			userBean.setDormitoryAddress(rs.getString("dormitory_address"));
			userBean.setEmail(rs.getString("email"));
			userBean.setSex(rs.getString("sex"));
			try {
				if(userBean.getSex().equals("1")){
					userBean.setSexName(BaseAction.getInstance().getMessages(MsgConstants.USER_PAGE_LABEL_USER_SEX_MAN));
				}else if(userBean.getSex().equals("0")){
					userBean.setSexName(BaseAction.getInstance().getMessages(MsgConstants.USER_PAGE_LABEL_USER_SEX_WOMAN));
				}
			} catch (Exception e) {
			}
			userBean.setMobile(rs.getString("mobile"));
			userBean.setUserType(rs.getString("user_type"));
			userBean.setUserTypeName(rs.getString("userTypeName"));
			try {
				if(userBean.getUserTypeName() == null || "null".equalsIgnoreCase(userBean.getUserTypeName())){
					if(userBean.getUserType().equals("1")){
						userBean.setUserTypeName("学生");//BaseAction.getInstance().getMessages(MsgConstants.USER_PAGE_LABEL_USER_TYPE_STUDENT));
					}else if(userBean.getUserType().equals("2")){
						userBean.setUserTypeName("专职教师");//BaseAction.getInstance().getMessages(MsgConstants.USER_PAGE_LABEL_USER_TYPE_FULL_TIME_TEACHER));
					}else if(userBean.getUserType().equals("3")){
						userBean.setUserTypeName("兼职教师");//BaseAction.getInstance().getMessages(MsgConstants.USER_PAGE_LABEL_USER_TYPE_PART_TIME_TEACHER));
					}else if(userBean.getUserType().equals("4")){
						userBean.setUserTypeName("外聘教师");//BaseAction.getInstance().getMessages(MsgConstants.USER_PAGE_LABEL_USER_TYPE_EXT_TEACHER));
					}
				}
			} catch (Exception e) {
			}
			userBean.setHomeAddress(rs.getString("home_address"));
			userBean.setBirtdate(rs.getString("birthdate"));
			if(userBean.getBirtdate() != null  && !userBean.getBirtdate().equals(""))
				userBean.setBirtdate(userBean.getBirtdate().substring(0,10));
			userBean.setZipCode(rs.getString("zip_code"));
			userBean.setAdmissionTime(rs.getString("admission_time"));
			if(userBean.getAdmissionTime() != null  && !userBean.getAdmissionTime().equals(""))
				userBean.setAdmissionTime(userBean.getAdmissionTime().substring(0,10));
			userBean.setFacultyId(rs.getString("faculty_id"));
			userBean.setSpecialtyId(rs.getString("specialty_id"));
			
			userBean.setEnrollment(rs.getFloat("enrollment"));
			userBean.setImage(rs.getString("image"));
			userBean.setGrade(rs.getString("grade"));
			userBean.setStatus(rs.getInt("status"));
			try {
				if(userBean.getStatus() == 0){
					userBean.setStatusVal(BaseAction.getInstance().getMessages(MsgConstants.USER_PAGE_LABEL_OK));
				}else if(userBean.getStatus() == -1){
					userBean.setStatusVal(BaseAction.getInstance().getMessages(MsgConstants.USER_PAGE_LABEL_DELETE));
				}else if(userBean.getStatus() == -2){
					userBean.setStatusVal(BaseAction.getInstance().getMessages(MsgConstants.USER_PAGE_LABEL_NO));
				}
			} catch (Exception e) {
			}
			userBean.setFaculty(rs.getString("facultyname"));
			userBean.setDescript(rs.getString("descript"));
			try {
				userBean.setGradeName(rs.getString("grade_type_name"));
			} catch (Exception e) {
			}
			try {
				userBean.setSpecialtyName(rs.getString("specialtyName"));
			} catch (Exception e) {
			}
			try{
				userBean.setTeachingGroupId(rs.getString("teaching_group_id"));
			}catch(Exception e){}
			
			try {
				userBean.setFile_path_id(rs.getInt("file_path_id"));
			} catch (Exception e) {
				// TODO: handle exception
			}
			
			return userBean;
		}
	}
	public class UsersSimpleMapper implements RowMapper {
		public Object mapRow(ResultSet rs, int i) throws SQLException {
			UserBean userBean = new UserBean();
			userBean.setId(rs.getInt("id"));
			userBean.setUserName(rs.getString("username"));
			return userBean;
		}
	}
}
